package com.ratta.supernote.plugin.sticker.lib.moudles;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;
import android.util.Log;

import com.facebook.react.bridge.Promise;
import com.facebook.react.bridge.ReactApplicationContext;
import com.ratta.supernote.plugin.sticker.lib.NativeStickerUtilsSpec;
import com.ratta.supernote.plugin.sticker.lib.utils.FileUtils;

import java.io.File;
import java.io.IOException;
import java.util.zip.ZipException;
import java.util.zip.ZipFile;

public class StickerUtils extends NativeStickerUtilsSpec {
    public StickerUtils(ReactApplicationContext reactContext) {
        super(reactContext);
    }

    @Override
    public void encodeStickerZip(String path, Promise promise) {


    }

    @Override
    public void decodeStickerZip(String path, Promise promise) {

    }

    @Override
    public void checkStickerZip(String path, Promise promise) {
        if(TextUtils.isEmpty(path)) {
            promise.resolve(false);
            return;
        }
        File file = new File(path);
        if(file.exists()) {
            try {
                new ZipFile(file);
                promise.resolve(true);
                return;
            } catch (ZipException e) {
                e.printStackTrace();
                promise.resolve(false);
                return;
            } catch (IOException e) {
                e.printStackTrace();
                promise.resolve(false);
                return;
            }
        }
        promise.resolve(false);
    }

    @SuppressLint("Range")
    @Override
    public void updateSystemDB(double version, Promise promise) {
        Log.i(NAME,"updateSystemDB version: " + version);
        String dbPath = getReactApplicationContext().getFilesDir() + "/plugins/a5db6166e8fe8e17/";
        String oldDbName = "system_stickers_db.db";
        String newDbName = "system_stickers_db_" + (int)version + ".db";

        try {
            // 检查文件是否存在
            File oldDb = new File(dbPath + oldDbName);
            File newDb = new File(dbPath + newDbName);

            // 旧数据不存在，新数据存在，重命名数据库文件
            if(!oldDb.exists() && newDb.exists()) {
                Log.i(NAME,"oldDb is not exists" );
                promise.resolve(FileUtils.renameToFile(dbPath + newDbName, dbPath + oldDbName));
                return;
            }
            // 旧数据库存在，新数据库不存在，不需要修改，直接返回
            if(oldDb.exists() && !newDb.exists()) {
                Log.i(NAME,"newDb is not exists" );
                promise.resolve(true);
                return;
            }
            if (!oldDb.exists() && !newDb.exists()) {
                Log.i(NAME,"newDb add oldDB is not exists" );
                promise.resolve(false);
                return;
            }

            // 打开两个数据库
            SQLiteDatabase oldDatabase = SQLiteDatabase.openDatabase(dbPath + oldDbName, null, SQLiteDatabase.OPEN_READWRITE);
            SQLiteDatabase newDatabase = SQLiteDatabase.openDatabase(dbPath + newDbName, null, SQLiteDatabase.OPEN_READONLY);

            // 开始事务
            oldDatabase.beginTransaction();
            try {

                Cursor cursorOld = oldDatabase.rawQuery("PRAGMA user_version", null);
                int oldVersion = 0;
                if(cursorOld.moveToFirst()) {
                    oldVersion = cursorOld.getInt(0);
                }
                Log.i(NAME,"Old database version: " + oldVersion);
                /// 新旧数据库版本相同，不需要更新
                if(oldVersion == version) {
                    Log.i(NAME,"NewVersion add old version is same!" );
                    FileUtils.deleteFile(dbPath + newDbName);
                    promise.resolve(true);
                    return;
                }
                // 1. 更新 StickerGroup 表
                Cursor groupCursor = newDatabase.query("StickerGroup", null, null, null, null, null, null);
                while (groupCursor.moveToNext()) {
                    ContentValues values = new ContentValues();
                    int id = groupCursor.getInt(groupCursor.getColumnIndex("id"));
                    values.put("id", id);
                    values.put("name", groupCursor.getString(groupCursor.getColumnIndex("name")));
                    values.put("num", groupCursor.getInt(groupCursor.getColumnIndex("num")));
                    values.put("path", groupCursor.getString(groupCursor.getColumnIndex("path")));
                    values.put("thumb_sticker", groupCursor.getString(groupCursor.getColumnIndex("thumb_sticker")));
                    values.put("dir_name", groupCursor.getString(groupCursor.getColumnIndex("dir_name")));
                    values.put("create_time", groupCursor.getLong(groupCursor.getColumnIndex("create_time")));
                    values.put("update_time", groupCursor.getLong(groupCursor.getColumnIndex("update_time")));
                    
                    // 检查是否存在相同的 id
                    Cursor existingGroup = oldDatabase.query("StickerGroup", null, 
                            "id = ?", new String[]{String.valueOf(id)}, null, null, null);
                    
                    if (existingGroup.moveToFirst()) {
                        // 更新现有记录
                        oldDatabase.update("StickerGroup", values, "id = ?", new String[]{String.valueOf(id)});
                    } else {
                        // 插入新记录，但不指定 id
                        values.remove("id");
                        oldDatabase.insert("StickerGroup", null, values);
                    }
                    existingGroup.close();
                }
                groupCursor.close();

                // 2. 更新 Sticker 表
                Cursor stickerCursor = newDatabase.query("Sticker", null, null, null, null, null, null);
                while (stickerCursor.moveToNext()) {
                    ContentValues values = new ContentValues();
                    int id = stickerCursor.getInt(stickerCursor.getColumnIndex("id"));
                    values.put("id", id);
                    values.put("name", stickerCursor.getString(stickerCursor.getColumnIndex("name")));
                    values.put("path", stickerCursor.getString(stickerCursor.getColumnIndex("path")));
                    values.put("thumb_sticker", stickerCursor.getString(stickerCursor.getColumnIndex("thumb_sticker")));
                    values.put("sticker_group_id", stickerCursor.getString(stickerCursor.getColumnIndex("sticker_group_id")));
                    values.put("create_time", stickerCursor.getLong(stickerCursor.getColumnIndex("create_time")));
                    values.put("update_time", stickerCursor.getLong(stickerCursor.getColumnIndex("update_time")));

                    // 检查是否存在相同的 id
                    Cursor existingSticker = oldDatabase.query("Sticker", null, 
                            "id = ?", new String[]{String.valueOf(id)}, null, null, null);
                    
                    if (existingSticker.moveToFirst()) {
                        // 更新现有记录
                        oldDatabase.update("Sticker", values, "id = ?", new String[]{String.valueOf(id)});
                    } else {
                        // 插入新记录，但不指定 id
                        values.remove("id");
                        oldDatabase.insert("Sticker", null, values);
                    }
                    existingSticker.close();
                }
                stickerCursor.close();
                // 更新数据库版本号
                oldDatabase.execSQL("PRAGMA user_version = " + (int)version);
                oldDatabase.setTransactionSuccessful();
                promise.resolve(true);

            } finally {
                Log.i(NAME,"db op finally" );
                oldDatabase.endTransaction();
                oldDatabase.close();
                newDatabase.close();
                FileUtils.deleteFile(dbPath + newDbName);

            }
        } catch (Exception e) {
            e.printStackTrace();
            promise.reject("DB_ERROR", e.getMessage());
        }
    }
}
